package com.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import com.blog.dto.Category;
import com.blog.util.DataAccess;
import com.blog.util.DateTime;

public class CategoryDao {
	/**
	 * 添加分类
	 * @param _cate
	 * @return
	 */
	public boolean addCategory(Category _cate) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into category values(null,?,?,null)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _cate.getCategory());
			ps.setInt(2, _cate.getCategory_order());
			if(ps.executeUpdate() == 1) flag=true;
			
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 查找所有的分类
	 * @return
	 */
	public Vector<Category> findAllCategory() {
		Vector<Category> v=new Vector<Category>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from category where delete_at  is  NULL  order by category_order desc  ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Category cate=new Category();
				cate.setCategory_id(rs.getInt("category_id"));
				cate.setCategory(rs.getString("category"));
				cate.setCategory_order(rs.getInt("category_order"));
				v.add(cate);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;	
	}
	
	/**
	 * 根据分类名称查找分类是否存在
	 * @return
	 */
	public boolean findCategoryByName(String _name ) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " select * from category where category=? AND delete_at  is  NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _name);
			if(ps.execute()) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据分类名称查找分类是否存在
	 * @return
	 */
	public Vector<Category> findCategoryById(int _id ) {
		Vector<Category> v=new Vector<Category>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from category where category_id=? AND delete_at  is  NULL ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			rs = ps.executeQuery();
			while (rs.next()) {
				Category cate=new Category();
				cate.setCategory_id(rs.getInt("category_id"));
				cate.setCategory(rs.getString("category"));
				cate.setCategory_order(rs.getInt("category_order"));
				v.add(cate);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(con , ps);
		}
		return v;
	}
	
	/**
	 * 修改分类
	 * @param _name
	 * @return
	 */
	public boolean editCategory(Category cate) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update category set category = ? , category_order = ?  where category_id = ? and (delete_at is null) " ;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, cate.getCategory());
			ps.setInt(2, cate.getCategory_order());
			ps.setInt(3, cate.getCategory_id());
			if(ps.executeUpdate()==1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据分类id修改分类
	 * @param _id
	 * @return
	 */
	public boolean editCategoryById(int _id , String _name) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update category set category = ?  where category_id = ? and (delete_at is null) " ;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _name);
			ps.setInt(2, _id);
			if(ps.executeUpdate()==1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据分类Id删除分类
	 * @param _id
	 * @return
	 */
	public boolean delCategoryById(int _id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update category set delete_at = ?  where category_id = ? and (delete_at is null) " ;
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, _id);
			if(ps.executeUpdate()==1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据分类名称删除分类
	 * @param _name
	 * @return
	 */
	public boolean delCategoryByName(String _name) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update category set delete_at = ?  where category = ? and (delete_at is null) " ;
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setString(2, _name);
			if(ps.executeUpdate()==1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(con , ps);
		}
		return flag;
	}
	
	
}
